<?php

namespace app\services\data\stat\ad\normal;

use app\clickhouse\models\Events;
use app\model\sdk\Orders;

class StatPayMidas extends StatBaseUserEvent
{
    public function getSql()
    {
        $join             = $this->queryJoin('users');
        $event_where      = $this->queryWhere(['time'], 'events');
        $user_where       = $this->queryWhere(['game_id', 'channel', 'game_channel_id', 'create_time'], 'users');
        $plan_where       = $this->queryWhere(['media_id', 'media_docking_id', 'owner', 'plan_group_id', 'plan_id', 'source_type'], 'ad_plan');
        $media_user_where = $this->queryWhere(['daili', 'media_user_id'], 'ad_media_user');
        $event_id         = Events::EVENT_PAY;
        $money_propname   = $this->moneyTypeName;
        $pay_platform     = Orders::PAY_PLATFORM_WECHAT_MIDAS;

        $sql = "
            SELECT count(DISTINCT events.`#user_id`) AS midas_pay_user, count(events.trade_no) AS midas_pay_num, sum(events.amount) AS midas_pay_money FROM(
                SELECT events.*, firstups.*, ad_plan.*, ad_media_user.* FROM users
                JOIN(
                    SELECT DISTINCT trade_no, `#user_id`, {$money_propname} as amount, status FROM sdk.events_local AS events
                    WHERE {$event_where}
                    AND `#event_id` = {$event_id}
                    AND amount > 0
                    AND is_test = 0
                    AND pay_platform = {$pay_platform}
                ) AS events
                ON events.`#user_id` = users.`#user_id`
                {$join}
                WHERE {$user_where}
                AND {$plan_where}
                AND {$media_user_where}
            )
        ";

        return $sql;
    }

    protected function queryGroupBy($table_name = 'users')
    {
        foreach ($this->groupbys as $name) {
            switch ($name) {
                case 'stat_hour':
                    $selects[] = "formatDateTime(toDateTime(events.`#create_time`), '%H', '{$this->timezone}') AS stat_hour";
                    break;
                case 'stat_date':
                    $selects[] = "toDate(events.`#create_time`, '{$this->timezone}') AS stat_date";
                    break;
                case 'stat_week':
                    $selects[] = "formatDateTime(toDateTime(events.`#create_time`), '%Y-%V', '{$this->timezone}') AS stat_week";
                    break;
                case 'stat_month':
                    $selects[] = "formatDateTime(toDateTime(events.`#create_time`), '%Y-%m', '{$this->timezone}') AS stat_month";
                    break;
                case 'media_id':
                case 'plan_id':
                case 'plan_group_id':
                case 'plan_owner_id':
                    $selects[] = "ad_plan.{$name} AS {$name}";
                    break;
                case 'daili_id':
                    $selects[] = "ad_media_user.{$name} AS {$name}";
                    break;
                case 'adgroup_id':
                    $selects[] = "firstups.adgroup_id AS {$name}";
                    break;
                case 'ad_id':
                    $selects[] = "firstups.ad_id AS {$name}";
                    break;
                case 'creative_id':
                    $selects[] = "firstups.creative_id AS {$name}";
                    break;
                case 'media_user_id':
                    $selects[] = "firstups.{$name} AS {$name}";
                    break;
                default:
                    $selects[] = "{$name} AS {$name}";
                    break;
            }
        }

        $select_str  = implode(',', $selects);
        $groupby_str = implode(',', $this->groupbys);

        return [$select_str, $groupby_str];
    }

    protected function userSelect()
    {
        $selects = [];
        foreach ($this->groupbys as $name) {
            switch ($name) {
                case 'stat_hour':
                case 'stat_date':
                case 'stat_week':
                case 'stat_month':
                case 'media_id':
                case 'plan_id':
                case 'plan_group_id':
                case 'plan_owner_id':
                case 'daili_id':
                case 'media_user_id':
                case 'adgroup_id':
                case 'ad_id':
                case 'creative_id':
                    break;
                default:
                    $selects[] = "{$name}";
                    break;
            }
        }

        $select_str = implode(',', $selects);

        return $select_str;
    }

    public function getSqlByGroup()
    {
        $join             = $this->queryJoin('users');
        $event_where      = $this->queryWhere(['time'], 'events');
        $user_where       = $this->queryWhere(['game_id', 'channel', 'game_channel_id', 'create_time', 'device_type'], 'users');
        $plan_where       = $this->queryWhere(['media_id', 'media_docking_id', 'owner', 'plan_group_id', 'plan_id', 'source_type'], 'ad_plan');
        $media_user_where = $this->queryWhere(['daili', 'media_user_id'], 'ad_media_user');
        $event_id         = Events::EVENT_PAY;
        $money_propname   = $this->moneyTypeName;
        $pay_platform     = Orders::PAY_PLATFORM_WECHAT_MIDAS;

        list($select_str, $groupby_str) = $this->queryGroupBy();
        $user_select_str = $this->userSelect();
        if ($user_select_str != '') {
            $user_select_str = ", {$user_select_str}";
        }

        $sql = "
            SELECT {$select_str}, count(DISTINCT events.`#user_id`) AS midas_pay_user, count(events.trade_no) AS midas_pay_num, sum(events.amount) AS midas_pay_money FROM(
                SELECT events.*, firstups.*, ad_plan.*, ad_media_user.* {$user_select_str} FROM users
                JOIN(
                    SELECT DISTINCT trade_no, `#create_time`, `#user_id`, {$money_propname} as amount, status FROM sdk.events_local AS events
                    WHERE {$event_where}
                    AND `#event_id` = {$event_id}
                    AND amount > 0
                    AND is_test = 0
                    AND pay_platform = {$pay_platform}
                ) AS events
                ON events.`#user_id` = users.`#user_id`
                {$join}
                WHERE {$user_where}
                AND {$plan_where}
                AND {$media_user_where}
            )
            GROUP BY {$groupby_str}
        ";

        return $sql;
    }
}
